package com.ksp.api.sqlscan;

import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import org.omg.CORBA.PRIVATE_MEMBER;
import org.springframework.web.servlet.mvc.method.annotation.ResponseBodyEmitterReturnValueHandler;

import com.ksp.util.FileUtil;

/**
 * 检查指定包下面的java文件中定义的静态成员变量的sql是否正确，
 * 检验的方式在数据库执行，没有异常，表示执行成功，有异常，报错
 * 
 * 
 * @author anye
 *
 */
public class KSPAPI_SqlScanUtil {

	static DBConn dbserver = new DBConnImpl();

	/**
	 * 01 校验包下的sql
	 * @param pWrkPth
	 */
	public static void chkPkgSql(String pWrkPth) {
		List<String> classNameLst = getClassNameLst(pWrkPth, null);
		//System.out.println(classNameLst);
		Map<String, Map<String, String>> wClsMap = new HashMap<>();
		for (String classStr : classNameLst) {
			try {
				Class<?> wClazz = Class.forName(classStr);
				Field[] fields = wClazz.getDeclaredFields();

				Map<String, String> wClsFldsMap = new HashMap<>();
				for (int i = 0; i < fields.length; i++) {
					Field field = fields[i];
					field.setAccessible(true);
					if (field.getType().toString().endsWith("java.lang.String")
							&& Modifier.isStatic(field.getModifiers())) {
						String sqlNam = field.getName();
						String sqlval = field.get(null).toString();
						wClsFldsMap.put(sqlNam, sqlval);
					}
				}
				wClsMap.put(classStr, wClsFldsMap);
			} catch (Exception e) {
				System.out.println(e.getMessage() + classStr);
				continue;
			}
		}
		System.out.println(wClsMap);
		// 
		dbserver.init();
		Connection conn = dbserver.getConnection();

		Iterator<Entry<java.lang.String, Map<java.lang.String, java.lang.String>>> wClsEntries = wClsMap.entrySet().iterator();
		while (wClsEntries.hasNext()) {
			Entry<java.lang.String, Map<java.lang.String, java.lang.String>> next = wClsEntries.next();
			validSql(conn, next.getKey(), next.getValue());
		}
	}
	
	/**
	 * 数据库采用连接池的方式
	 * **/
	
	
	
	
	
	
	
	
	/**
	 * 按java类名为一个单位，检查该类中sql语句的正确性
	 * @param conn
	 * @param pClsNam
	 * @param pFldMap
	 */
	public static void validSql(Connection conn, String pClsNam, Map<String, String> pFldMap) {
		Iterator<Entry<String, String>> iterator = pFldMap.entrySet().iterator();
		while (iterator.hasNext()) {
			Entry<String, String> next = iterator.next();
			String fldNam = next.getKey();
			String sql = next.getValue();
			sql = sql.replace("?", " ");
			PreparedStatement pst;
			try {
				pst = conn.prepareStatement(sql);
				if (sql.toLowerCase().startsWith("select")) {
					ResultSet rs = pst.executeQuery();
				} else if (sql.toLowerCase().startsWith("insert")) {
					int cnt = pst.executeUpdate();
				} 
				pst.close();
				//System.out.println(pClsNam + "," + fldNam + "执行成功");
			} catch (SQLException e) {
				System.err.println(pClsNam + "," + fldNam + "执行失败," + e.getMessage());
			} finally {
				dbserver.close(conn);
			}
		}
	}
	
	/**
	 * 获取指定包下的java文件
	 * @param filPth
	 * @param classname
	 * @return
	 */
	public static List<String> getClassNameLst(String filPth, List<String> classname) {
		List<String> wClassNam = new ArrayList<>();
		File file = new File(filPth);
		File[] childFils = file.listFiles();
		for (File childFil : childFils) {
			String wChdPth = childFil.getPath();
			if (childFil.isDirectory()) {
				wClassNam.addAll(getClassNameLst(wChdPth, wClassNam));
			} else {
				
				wChdPth = wChdPth.substring(wChdPth.indexOf("main\\java") + 10, wChdPth.lastIndexOf("."));
				wChdPth = wChdPth.replace("\\", ".");
				wClassNam.add(wChdPth);
			}
		}
		
		return wClassNam;
	}

	public static void main(String[] args) {
		//获取指定包下的java类全名
		String pkgPth = "src/main/java/com/ksp";
		chkPkgSql(pkgPth);
		
	}
}
